package cn.edu.cug.cs.gtl.utils.impl;

import cn.edu.cug.cs.gtl.protos.DataItem;
import cn.edu.cug.cs.gtl.protos.DataKey;
import cn.edu.cug.cs.gtl.protos.DataLink;
import cn.edu.cug.cs.gtl.utils.DataItemManager;
import cn.edu.cug.cs.gtl.utils.DataLinkManager;
import cn.edu.cug.cs.gtl.utils.DatabaseUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DataLinkManagerImpl implements DataLinkManager {

    private Connection con;

    DataItemManager dataItemManager=new DataItemManagerImpl();

    public DataLinkManagerImpl() throws SQLException {
        con= DatabaseUtil.getConnection();
    }

    @Override
    public void insertDataLink(DataLink dataLink) {
        String insertSQL="insert into data_link(name,alias,description,transform,sid,eid) values(?,?,?,?,?,?)";

        try {
            PreparedStatement preparedStatement = con.prepareStatement(insertSQL);
            preparedStatement.setString(1, dataLink.getName());
            preparedStatement.setString(2, dataLink.getAlias());
            preparedStatement.setString(3, dataLink.getDescription());
            preparedStatement.setString(4, dataLink.getTransform());

            int sid=dataItemManager.getIdByName(dataLink.getStartItemName());
            int eid=dataItemManager.getIdByName(dataLink.getEndItemName());
            preparedStatement.setInt(5,sid);
            preparedStatement.setInt(6,eid);


            int rowsAffected = preparedStatement.executeUpdate();

            //检查是否成功插入一条记录
            if(rowsAffected>0){
                System.out.println("插入成功");
            }
            else{
                System.err.println("插入失败");
            }

            preparedStatement.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public DataLink removeDataLink(String dataLinkName) {
        //首先检查是否存在指定的数据项
        DataLink existingDataLink=getDataLink(dataLinkName);

        if(existingDataLink!=null){
            String deleteSQL="DELETE from data_link where name=?";

            try{
                PreparedStatement preparedStatement = con.prepareStatement(deleteSQL);

                preparedStatement.setString(1,dataLinkName);

                int rowsAffected = preparedStatement.executeUpdate();

                // 检查是否成功删除了一行记录
                if (rowsAffected > 0) {
                    System.out.println(dataLinkName + "删除成功");
                    return existingDataLink;
                } else {
                    System.err.println(dataLinkName + "删除失败");
                }

                preparedStatement.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }

        else{
            System.err.println(dataLinkName+"不存在");
        }
        return null;
    }

    @Override
    public void updateDataLink(DataLink dataLink) {
        String updateSQL="update data_link set alias=?,description=?,transform=?,sid=?,eid=? where name=?";

        try {
            PreparedStatement preparedStatement = con.prepareStatement(updateSQL);

            preparedStatement.setString(1,dataLink.getAlias());
            preparedStatement.setString(2,dataLink.getDescription());
            preparedStatement.setString(3,dataLink.getTransform());

            int sid=dataItemManager.getIdByName(dataLink.getStartItemName());
            int eid= dataItemManager.getIdByName(dataLink.getEndItemName());
            preparedStatement.setInt(4,sid);
            preparedStatement.setInt(5,eid);


            preparedStatement.setString(6,dataLink.getName());

            int rowsAffected = preparedStatement.executeUpdate();

            // 检查是否成功更新了一行记录
            if (rowsAffected > 0) {
                System.out.println(dataLink.getName() + "更新成功");
            } else {
                System.err.println(dataLink.getName() + "更新失败");
            }

            preparedStatement.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public DataLink getDataLink(String dataLinkName) {
        String getSQL="select * from data_link where name=?";


        try {
            PreparedStatement preparedStatement = con.prepareStatement(getSQL);
            preparedStatement.setString(1,dataLinkName);

            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                String alias=resultSet.getString("alias");
                String description=resultSet.getString("description");
                String transform=resultSet.getString("transform");

                // 通过getNameById方法，获取对应的dataItemName
                String startItemName = dataItemManager.getNameById(resultSet.getInt("sid"));
                String endItemName = dataItemManager.getNameById(resultSet.getInt("eid"));


                // 创建 DataLink 对象并设置属性值
                DataLink dataLink= DataLink.newBuilder()
                        .setName(dataLinkName)
                        .setAlias(alias)
                        .setDescription(description)
                        .setTransform(transform)
                        .setStartItemName(startItemName)
                        .setEndItemName(endItemName)
                        .build();

                resultSet.close();
                preparedStatement.close();

                return dataLink;
            }else{
                System.err.println(dataLinkName+"不存在");
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return null;

    }

    @Override
    public DataLink getDataLink(String startDataItemName, String endDataItemName) {
        String getSQL="select * from data_link where sid=? and eid=?";


        try {
            PreparedStatement preparedStatement = con.prepareStatement(getSQL);

            // 获取sid、eid
            int sid=dataItemManager.getIdByName(startDataItemName);
            int eid=dataItemManager.getIdByName(endDataItemName);

            preparedStatement.setInt(1,sid);
            preparedStatement.setInt(2,eid);

            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                String name=resultSet.getString("name");
                String alias=resultSet.getString("alias");
                String description=resultSet.getString("description");
                String transform=resultSet.getString("transform");


                // 创建 DataLink 对象并设置属性值
                DataLink dataLink= DataLink.newBuilder()
                        .setName(name)
                        .setAlias(alias)
                        .setDescription(description)
                        .setTransform(transform)
                        .setStartItemName(startDataItemName)
                        .setEndItemName(endDataItemName)
                        .build();

                resultSet.close();
                preparedStatement.close();

                return dataLink;
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return null;
    }

    @Override
    public List<DataItem> getInDataItems(String dataItemName) {
        String getStartItemSQL="select sid from data_link where eid=?";
        String getDataItemSQL="select * from data_item where id=?";

        try{
            //1.获取相关的start_item_name
            PreparedStatement getStartItemStatement = con.prepareStatement(getStartItemSQL);

            int eid=dataItemManager.getIdByName(dataItemName);

            getStartItemStatement.setInt(1,eid);
            ResultSet startItemResultSet = getStartItemStatement.executeQuery();

            List<DataItem> dataItems=new ArrayList<>();

            //2.对每个start_item_name执行data_item表的查询，并存入List中
            while(startItemResultSet.next()){
                int sid=startItemResultSet.getInt("sid");

                //查询data_item表
                PreparedStatement getDataItemStatement= con.prepareStatement(getDataItemSQL);
                getDataItemStatement.setInt(1,sid);
                ResultSet dataItemResultSet = getDataItemStatement.executeQuery();

                //构建data_item对象
                if(dataItemResultSet.next()){
                    DataItem dataItem = DataItem.newBuilder()
                            .setName(dataItemResultSet.getString("name"))
                            .setAlias(dataItemResultSet.getString("alias"))
                            .setDescription(dataItemResultSet.getString("description"))
                            .setFormat(dataItemResultSet.getString("format"))
                            .setKey(DataKey.parseFrom(dataItemResultSet.getBytes("data_key")))
                            .build();

                    dataItems.add(dataItem);
                }

                //关闭ResultSet和Statement
                dataItemResultSet.close();
                getDataItemStatement.close();
            }

            //关闭ResultSet和Statement
            startItemResultSet.close();
            getStartItemStatement.close();

            return dataItems;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<DataItem> getOutDataItems(String dataItemName) {
        String getEndItemSQL="select eid from data_link where sid=?";
        String getDataItemSQL="select * from data_item where id=?";

        try{
            //1.获取相关的end_item_name
            PreparedStatement getEndItemStatement = con.prepareStatement(getEndItemSQL);

            int sid=dataItemManager.getIdByName(dataItemName);

            getEndItemStatement.setInt(1,sid);
            ResultSet endItemResultSet = getEndItemStatement.executeQuery();

            List<DataItem> dataItems=new ArrayList<>();

            //2.对每个end_item_name执行data_item表的查询，并存入List中
            while(endItemResultSet.next()){
                int eid=endItemResultSet.getInt("eid");

                //查询data_item表
                PreparedStatement getDataItemStatement= con.prepareStatement(getDataItemSQL);
                getDataItemStatement.setInt(1,eid);
                ResultSet dataItemResultSet = getDataItemStatement.executeQuery();

                //构建data_item对象
                if(dataItemResultSet.next()){
                    DataItem dataItem = DataItem.newBuilder()
                            .setName(dataItemResultSet.getString("name"))
                            .setAlias(dataItemResultSet.getString("alias"))
                            .setDescription(dataItemResultSet.getString("description"))
                            .setFormat(dataItemResultSet.getString("format"))
                            .setKey(DataKey.parseFrom(dataItemResultSet.getBytes("data_key")))
                            .build();

                    dataItems.add(dataItem);
                }

                //关闭ResultSet和Statement
                dataItemResultSet.close();
                getDataItemStatement.close();
            }

            //关闭ResultSet和Statement
            endItemResultSet.close();
            getEndItemStatement.close();

            return dataItems;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
}
